package shellstation;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class ProductController {
    
    private Connection conn = null;
    private Statement stmt;
    private ArrayList arrayID = new ArrayList();
    
    public ProductController(){
    
    }
    
    public void ConnectToDatabase(){
        String url = "jdbc:mysql://localhost:3306/";
        String dbName = "shellstation";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root"; 
        String password = "p@ssword";
        
        try{
          
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url + dbName, userName, password);
           
        }
        
        catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public ResultSet getSizes(){
        
        ResultSet rs;
        
        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT DISTINCT size from inventory ");
        
            
        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }
        
        return rs;
    }

    public ResultSet getProduct(String size){

       ResultSet rs;
       
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT *  from inventory where size=" + "'" +size + "';");
           
        }

         catch (Exception e){
            e.printStackTrace();
            return null;
        }
       
        return rs;
    }
    
    public void getAddProduct(String productName, String status, String size, String price, String stock){
        
        ResultSet rs;
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            System.out.println("INSERT INTO inventory (productname, size, price ,stock, status) VALUES ('" + productName + "', '" + size + "', " 
                    + price + ", " + stock + ", '" + status + "');");
            stmt.executeUpdate("INSERT INTO inventory (productname, size, price ,stock, status) VALUES ('" + productName + "', '" + size + "', " 
                    + price + ", " + stock + ", '" + status + "');");
           
        }

         catch (Exception e){
            e.printStackTrace();
        }

    }
    
    public void getUpdateProduct(String productName, String status, String price, int stock, int ID){
        
        ResultSet rs;
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            System.out.println("UPDATE inventory SET productName = '" + productName + "', price = " 
                    + price + ", stock = " + stock + ", status = '" + status + "' WHERE productID = " + ID);
            stmt.executeUpdate("UPDATE inventory SET productName = '" + productName + "', price = " 
                    + price + ", stock = " + stock + ", status = '" + status + "' WHERE productID = " + ID);
           
        }

         catch (Exception e){
            e.printStackTrace();
        }

    }
    
    public ResultSet getProductTable(String productName){

        ResultSet rs;

        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT *  from inventory where productName LIKE " + "'%" + productName + "%'");
           
        }

         catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }
    public int getProductID(String productname, String size){

        ResultSet rs;
        int prodId=0 ;
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            System.out.println("SELECT productID FROM inventory WHERE productname=" + "'" + productname + "'" +
                                    " AND " + "size=" + "'" + size + "';");
            rs = stmt.executeQuery("SELECT productID FROM inventory WHERE productname=" + "'" + productname + "'" +
                                    " AND " + "size=" + "'" + size + "';");
            rs.next();
            prodId= rs.getInt("productID");
        }

         catch (Exception e){
            e.printStackTrace();

        }

        

        return prodId;

    }
    
    public ResultSet getProductData(int ID){

        ResultSet rs;

        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT *  from inventory where productID = " + ID + ";");
           
        }

         catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }
    
    public void getQuantityUpdate(int productID, int stock){

        ResultSet rs;

        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date date = new Date();
            String d = dateFormat.format(date);
            System.out.println("INSERT INTO stockdetails (productID, quantity, stock_date) VALUES (" 
                    + productID + ", " + stock + ", '" + d + "');");
                                   
            stmt.executeUpdate("INSERT INTO stockdetails (productID, quantity, stock_date) VALUES (" 
                    + productID + ", " + stock + ", '" + d +"');");

        }

         catch (Exception e){
            e.printStackTrace();

        }


    }
    public ResultSet getAllProduct(){

       ResultSet rs;
       
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT *  from inventory;");
           
        }

         catch (Exception e){
            e.printStackTrace();
            return null;
        }
       
        return rs;
    }
    
    public ResultSet getSizeUnit(){

       ResultSet rs;
       
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT sizeUnit  from size;");
           
        }

         catch (Exception e){
            e.printStackTrace();
            return null;
        }
       
        return rs;
    }
    
    public void getAddSizeUnit(String sizeUnit){
        
        ResultSet rs;
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            System.out.println("INSERT INTO size (sizeUnit) VALUES ( '" + sizeUnit + "');");
            stmt.executeUpdate("INSERT INTO size (sizeUnit) VALUES ( '" + sizeUnit + "');");
           
        }

         catch (Exception e){
            e.printStackTrace();
        }

    }
    
    public ResultSet getSizeID(String sizeUnit){

        ResultSet rs;
        int prodId=0 ;
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            System.out.println("SELECT sizeID FROM size WHERE sizeUnit =" + "'" + sizeUnit + "';");
            rs = stmt.executeQuery("SELECT sizeID FROM size WHERE sizeUnit =" + "'" + sizeUnit + "';");
            while(rs.next())
                return rs;
        }

         catch (Exception e){
            e.printStackTrace();
            return null;

        }

        
      
        return null;

    }
    
    public ResultSet getProductCopy(String productname, String size){

        ResultSet rs;
        int prodId=0 ;
        try{

            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            System.out.println("SELECT * FROM inventory WHERE productname=" + "'" + productname + "'" +
                                    " AND " + "size=" + "'" + size + "';");
            rs = stmt.executeQuery("SELECT * FROM inventory WHERE productname=" + "'" + productname + "'" +
                                    " AND " + "size=" + "'" + size + "';");
            while(rs.next())
                return rs;
        }

         catch (Exception e){
            e.printStackTrace();
            return null;

        }

        
      
        return null;

    }
}
